package com.gimi.cloud.bbp.dao.dao.User;

import com.gimi.cloud.bbp.dao.myMapper.MyMapper;
import com.gimi.cloud.bbp.dto.user.UserDTO;
import com.gimi.cloud.bbp.entity.user.User;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * Create by xiankejin on 2017/10/13
 */
@Mapper
public interface UserDao extends MyMapper<User> {

    @Select("SELECT user_id as userId,user_name AS userName,pwd,mobile_phone as mobilePhone,add_time as addTime FROM rd_user WHERE user_name = #{phone} OR mobile_phone = #{phone} LIMIT 1;")
    User checkPhoneAvailable(@Param(value = "phone") String phone);

    @Select("SELECT COUNT(1) FROM rd_user WHERE user_name = #{userName};")
    int countByUserName(@Param(value = "userName") String userName);

    @Select("SELECT user_id as userId,user_name AS userName,pwd,pay_pwd AS payPwd,real_name as realName,email,mobile_phone as phone,card_id as cardId,add_time as addTime FROM rd_user WHERE email = #{email} LIMIT 1;")
    User getUserByEmail(@Param(value = "email") String phone);
    
    @Select("SELECT user_id as userId,user_name AS userName,pwd,pay_pwd AS payPwd,real_name as realName,email,mobile_phone as phone,card_id as cardId,add_time as addTime FROM rd_user  WHERE card_id = #{cardId} LIMIT 1;")
    User getUserBycardId(@Param(value = "cardId") String cardId);

    @Select("SELECT user_id as userId,user_name AS userName,pwd,pay_pwd AS payPwd,real_name as realName,email,mobile_phone as phone,card_id as cardId,add_time as addTime FROM rd_user WHERE user_id = #{userId} LIMIT 1;")
    User getUserByUserId(@Param(value = "userId") String userId);

    //这里表userId为int类型
    @Select("SELECT `user_name`,`mobile_phone`,`device_type` FROM rd_user WHERE user_id = #{userId} LIMIT 1")
    UserDTO getUserByUserIid(@Param(value = "userId") Integer userId);
    
    @Select("UPDATE rd_user SET pwd = #{pwd} WHERE user_id = #{userId}")
    User modifyPwd(@Param("pwd") String pwd, @Param("userId") String userId);

    @Update("UPDATE rd_user SET user_name = #{userName} WHERE user_id = #{userId}")
    int modifyUsername(@Param(value = "userName") String userName, @Param(value = "userId") String userId);

    @Update("UPDATE rd_user SET mobile_phone = #{mobilePhone} WHERE user_id = #{userId}")
    int modifyPhone(@Param(value = "mobilePhone") String mobilePhone, @Param(value = "userId") String userId);

    @Select("select * from rd_user where user_name = #{userName}")
    User getUserByUserName(@Param(value = "userName") String userName);

    @Select("SELECT date_format(add_time,'%m-%d') AS `day`, COUNT(1) AS `count` FROM rd_user WHERE add_time >= DATE(NOW()) - INTERVAL 6 DAY GROUP BY DAY(add_time)")
    List<Map<String, Object>> getWeekRegisteredNum();

    @Select("SELECT DATE_FORMAT(add_time,'%H') AS `time`, COUNT(1) AS `count` FROM rd_user WHERE DATE(add_time) = DATE(NOW()) GROUP BY HOUR(add_time)")
    List<Map<String,Object>> get24HourRegisteredCount();

    @Select("SELECT DATE_FORMAT(add_time,'%H') AS `time`, COUNT(1) AS `count` FROM rd_user WHERE DATE(add_time) = DATE(NOW() - INTERVAL 1 DAY) GROUP BY HOUR(add_time)")
    List<Map<String,Object>> get48To24HourRegisteredCount();
    /**
     * 插入一条，且返回ID
     * @param isUser
     * @return
     */
    @Options( useGeneratedKeys = true,keyProperty = "userId",keyColumn = "user_id")
    @Insert("INSERT INTO rd_user ( `user_id`,`user_name`,`add_time`) VALUES " +
            "(#{userId},#{userName},#{addTime})")
    int insertReturnUserId(User isUser);
    
    @Select("select mobile_phone from rd_user where user_id in (${userIds})")
    List<String> getPhoneByUserIds(@Param("userIds") String userIds);
}
